﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using iBing.Common.DbHelper;
using iBing.Common.DbHelper.CoreObj;
using CodeCreator.Template;
using CodeCreator.CoreObj.DbManager;
using CodeCreator;

namespace iBing.Common.DbHelper
{
    public class DbHelperForOracle : IDbHelper
    {
        private string connectionString = "";
        public DbHelperForOracle(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public int ExecuteSql(string SQLString)
        {
            using (connection = new OracleConnection(connectionString))
            {
                using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.OracleClient.OracleException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        public object GetSingle(string SQLString)
        {
            using (connection = new OracleConnection(connectionString))
            {
                using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.OracleClient.OracleException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        public IDataReader ExecuteReader(string strSQL)
        {
            connection = new OracleConnection(connectionString);
            OracleCommand cmd = new OracleCommand(strSQL, connection);
            try
            {
                connection.Open();
                IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.OracleClient.OracleException e)
            {
                throw new Exception(e.Message);
            }

        }

        public DataSet Query(string SQLString)
        {
            using (connection = new OracleConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.OracleClient.OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        OracleConnection connection = null;
        public void CloseConnection()
        {
            try
            {
                if (this.connection != null && this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
            }
            catch
            { }
        }

        private string objectToString(object obj)
        {
            if (obj != null)
                return obj.ToString();
            else
                return "";
        }  

        public List<string> GetAllSchema()
        {
            List<string> result = new List<string>();
            DataSet ds = Query("select distinct owner as SCHEMA from all_tables");
            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    result.Add(this.objectToString(dr["SCHEMA"]));
                }
            }
            return result;
        }

        public List<string> GetTablesBySchema(string schemaName)
        {
            List<string> result = new List<string>();
            DataSet ds = Query("select TABLE_NAME from all_tables where owner='" + schemaName + "' union all select VIEW_NAME as TABLE_NAME from  all_views where owner='" + schemaName + "'");
             if (ds != null && ds.Tables.Count > 0)
             {
                 foreach (DataRow dr in ds.Tables[0].Rows)
                 {
                     result.Add(this.objectToString(dr["TABLE_NAME"]));
                 }
             }
             return result;
        }

        public List<DbField> GetColumnsByTable(string schemaName, string tableName)
        {
            string strSql = "select A.COLUMN_NAME,A.DATA_TYPE,A.DATA_LENGTH,NVL(B.ISPK,0) ISPK ";
            strSql += " from  ";
            strSql += " (select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from all_tab_columns where OWNER='" + schemaName + "' and TABLE_NAME='" + tableName + "' order by COLUMN_ID asc ) A ";
            strSql += " left join  ";
            strSql += " (select column_name,1 ISPK from all_cons_columns where constraint_name in   ";
            strSql += " (select constraint_name from all_constraints where CONSTRAINT_TYPE='P' and OWNER='" + schemaName + "' and TABLE_NAME='" + tableName + "') and OWNER='" + schemaName + "' ) B ";
            strSql += " on A.COLUMN_NAME=B.COLUMN_NAME";
            //构建表的字段
            List<DbField> fields = new List<DbField>();
            DataSet ds = Query(strSql);

            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow dc in ds.Tables[0].Rows)
                {
                    DbField of = new DbField();
                    of.FieldName = objectToString(dc["COLUMN_NAME"]);
                    of.FieldType = objectToString(dc["DATA_TYPE"]);
                    of.FieldLength = objectToString(dc["DATA_LENGTH"]);
                    if (objectToString(dc["ISPK"]) == "1")
                    {
                        of.IsPk = true;
                    }
                    fields.Add(of);
                }
            }

            return fields;

        }
    }
}
